﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using BiocityFilmquizAdmin.Src.Domain;
using BiocityFilmquizAdmin.Src.ftp;
using MySql.Data.MySqlClient;
using BiocityFilmquizAdmin.Src.Security;
using BiocityFilmquizAdmin.Src.Mail;
using BiocityFilmquizAdmin.Src.IMDB;

namespace BiocityFilmquizAdmin.Src.DatabaseAccess
{
    public class AdminHelper
    {
        private DbAcces dbAccess;
        private static AdminHelper instance = null;

        public AdminHelper()
        {
            dbAccess = DbAcces.getInstance();
        }

        public static AdminHelper getInstance()
        {
            if (instance == null)
            {
                instance = new AdminHelper();
            }

            return instance;
        }

        public List<Round> getAllRounds()
        {
            String query = "SELECT * FROM rounds;";

            List<Round> rounds = new List<Round>(); 
            MySqlDataReader reader = dbAccess.Select(query);

            Round round;

            while (reader.Read())
            {
                round = new Round(reader.GetInt32(0), reader.GetDateTime(1));
                rounds.Add(round);
            }

            dbAccess.CloseConnection();
            reader.Close();

            return rounds;
        }

        public List<string> getAllAnswersForQuestion(int question, int round)
        {
            String query = "SELECT * FROM questions WHERE question = " + question + " AND round = " + round + ";";

            MySqlDataReader reader = dbAccess.Select(query);

            List<string> answers = new List<string>();

            while (reader.Read())
            {
                answers.Add(reader.GetString(2));
            }

            reader.Close();
            dbAccess.CloseConnection();

            return answers;
        }

        public List<string> getAllAnswersForExtra(int question, int round)
        {
            String query = "SELECT * FROM extraQuestions WHERE extraQuestionID = " + question + " AND round = " + round + ";";

            MySqlDataReader reader = dbAccess.Select(query);

            List<string> answers = new List<string>();

            while (reader.Read())
            {
                answers.Add(reader.GetString(4));
            }

            reader.Close();
            dbAccess.CloseConnection();

            return answers;
        }

        public List<User> getAllUsers()
        {
            string query = "select * from users";
            MySqlDataReader reader = dbAccess.Select(query);
            List<User> users = new List<User>();
            while(reader.Read())
            {
                User user = new User(false);
                user.userID = reader.GetInt32(0);
                user.name = reader.GetString(1);
                user.email = reader.GetString(3);
                user.admin = reader.GetBoolean(4);

                users.Add(user);
            }
            reader.Close();
            dbAccess.CloseConnection();

            return users;
        }

        public Round getRoundById(int roundId)
        {
            String query = "SELECT * FROM rounds where roundID = " + roundId +  ";";

            MySqlDataReader reader = dbAccess.Select(query);

            Round round = null;

            if (reader.Read())
            {
                round = new Round(reader.GetInt32(0), reader.GetDateTime(1));
            }

            dbAccess.CloseConnection();
            reader.Close();

            return round;
        }

        public void insertNewAnswer(string answer, int image, int round)
        {
            answer = answer.Replace("'", "''");
            string query = "INSERT INTO questions (`question`, `answer`, `round`) VALUES (" + image + ", '" + answer + "', " + round + ");";
            dbAccess.Insert(query);

            List<string> liste = getAllAnswersForQuestion(image, round);

            if (liste.Count == 1)
            {
                IMDb imdb = new IMDb(answer);

                query = "INSERT INTO movieInfo (`questionid`, `roundid`, `year`, `imdbLink`) VALUES (" + image + ", " + round + ", '" + imdb.Year + "', '" + imdb.ImdbURL + "');";
                dbAccess.Insert(query);
            }
        }

        public void insertNewExtraAnswer(string answer, int extra, int round, int releasedAt)
        {
            answer = answer.Replace("'", "''");
            string query = "INSERT INTO extraQuestions (`extraQuestionID`, `answer`, `round`, `releasedAt`) VALUES (" + extra + ", '" + answer + "', " + round + ", " + releasedAt + ");";
            dbAccess.Insert(query);

            List<string> liste = getAllAnswersForExtra(extra, round);

            if (liste.Count == 1)
            {
                IMDb imdb = new IMDb(answer);

                query = "INSERT INTO movieInfo (`questionid`, `roundid`, `year`, `imdbLink`, `movieInfoID`) VALUES (" + (extra + 900) + ", " + round + ", '" + imdb.Year + "', '" + imdb.ImdbURL + "', 1);";
                dbAccess.Insert(query);
            }
        }

        public void deleteAnswer(string answer, int image, int round)
        {
            string query = "DELETE FROM questions WHERE question = " + image + " and answer = '" + answer + "' and round =" + round + ";";
            dbAccess.Delete(query);
        }

        public void deleteExtraAnswer(string answer, int question, int round)
        {
            string query = "DELETE FROM extraQuestions WHERE extraQuestionID = " + question + " and answer = '" + answer + "' and round =" + round + ";";
            dbAccess.Delete(query);
        }

        public void createNewRound(int roundId, DateTime end)
        {
            string query = "insert into rounds values(" + roundId + ", '" + end.ToString("yyyy-MM-dd") + "');";
            dbAccess.Insert(query);

            FtpHelper.getInstance().createNewRoundFolder(roundId);
        }

        public void updateEndDateOnRound(int round, DateTime date)
        {
            string query = "update rounds set end = '" + date.ToString("yyyy-MM-dd") + "' where roundId = " + round + ";";
            dbAccess.Update(query);
        }

        public string getProperty(string label)
        {
            string query = "select propertyValue from properties where idproperties = '" + label + "';";
            MySqlDataReader reader = dbAccess.Select(query);
            reader.Read();
            string message = reader.GetString(0);
            reader.Close();
            dbAccess.CloseConnection();

            return message;
        }

        public void setProperty(string label, string text)
        {
            text = text.Replace("'", "''");
            string query = "update properties set propertyValue = '" + text + "' where idproperties = '" + label + "';";
            dbAccess.Update(query);
        }

        public void deleteUserEntirely(int userId)
        {
            string query = "delete from users where userID = " + userId + ";";
            dbAccess.Delete(query);
            query = "delete from answers where user = " + userId + ";";
            dbAccess.Delete(query);
            query = "delete from comments where userId = " + userId + ";";
            dbAccess.Delete(query);
            query = "delete from playersCompleted where user = " + userId + ";";
            dbAccess.Delete(query);
            query = "delete from wrongAnswers where user = " + userId + ";";
            dbAccess.Delete(query);
        }

        public int getReleasedAt(int extraID, int round)
        {
            string query = "select releasedAt from extraQuestions where extraQuestionID = " + extraID + " and round = " + round + ";";
            MySqlDataReader reader = dbAccess.Select(query);
            if(reader.Read()){
            int releasedAt = reader.GetInt32(0);
            reader.Close();
            dbAccess.CloseConnection();

            return releasedAt;
            }
            else
            {
                reader.Close();
                dbAccess.CloseConnection();

                return -1;
            }
        }

        public void deleteMovieInfo(int question, int round)
        {
            string query = "delete from movieInfo where questionid = " + question + " and roundid = " + round + ";";
            dbAccess.Delete(query);
        }
    }
}